<?php

namespace App\Models\Customer;

use App\Models\Admin\AssignLog;
use App\Models\Admin\CustomerGroup;
use App\Models\User\UserBase;
use Carbon\Carbon;
use function foo\func;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class CustomerBase extends Model
{
    protected $table_name='customer';

    /* 通过ID获取客户基础数据 */
    public function getCustomerByID($id)
    {
        $res = DB::table($this->table_name);
        if(is_array($id)){
            $result = $res->whereIn('id',$id)->get();
        }else{
            $result = $res->where('id',$id)->first();
        }
        if(!$result){
            return false;
        }
        $result = json_decode(json_encode($result),true);
        if(!is_array($result) || count($result)<1){
            return false;
        }
        return $result;
    }

    /* 通过ID获取客户详细数据 */
    public function getCustomerDetail($id)
    {
        $result = DB::table($this->table_name)->where('id',$id)->first();
        if(!$result){
            return false;
        }
        $result = json_decode(json_encode($result),true);
        $res = DB::table('member as m')
            ->leftJoin('member_extend as me','me.member_id','=','m.id')
            ->select('me.avatar', "me.realname", "me.addperson", "me.recommend", "me.cash_coupon", "me.balance",
                "m.name", "m.mobile", "m.active_time")
            ->where("m.id", $id)
            ->first();
        $res = json_decode(json_encode($res),true);
        if(!is_array($result) || count($result)<1){
            return false;
        }
        if(is_array($res))
            $result = array_merge($result, $res);
        else{
            $result["avatar"] = $result["cash_coupon"] = $result["balance"] = $result["active_time"] = "";
        }
        return $result;
    }

    public function getOverCustomerCount($id, $date){
        $res = DB::table($this->table_name);
        $res->where("contact_next_time", "<", $date)->where('mall_id', request()->mallData["id"]);
        if(is_array($id)){
            $result = $res->whereIn('recommend',$id)->count();
        }else{
            $result = $res->where('recommend',$id)->count();
        }
        return $result;
    }

    /* 通过ID获取客户详细数据 */
    public function getCustomerDetailByID($id)
    {
        $res = DB::table($this->table_name.' as c')
            ->select('c.*','au.name as admin_name')
            ->leftJoin('admin_users as au','au.id','=','c.recommend')
            ->where('c.id',$id)
            ->first();
        if(!$res){
            return false;
        }
        $res = json_decode(json_encode($res),true);
        if(!is_array($res) || count($res)<1){
            return false;
        }
        return $res;
    }

    /* 通过手机号获取客户 */
    public function getCustomerByMobile($mobile,$id=null, $all = false)
    {
        $res = DB::table($this->table_name.' as c')
            ->leftJoin('admin_users as a','c.recommend','=','a.id')
            ->select('a.name', "c.mobile", "c.spare_mobile", "c.id")
            ->where("c.mall_id", request()->mallData["id"]);
        $res->where(function ($query) use ($mobile) {
            $query->where("c.mobile", "REGEXP", "(^|,)(".implode("|", $mobile).")($|,)")
                ->orWhere("c.spare_mobile", "REGEXP", "(^|,)(".implode("|", $mobile).")($|,)");
        });
        if ($id != ''){
            $res->where('c.id','<>',$id);
        }
        $result = $all ? $res->get() : $res->orderBy("id", "desc")->first();
        $result = json_decode(json_encode($result),true);
        return $result;
    }

    /* 通过QQ号获取客户 */
    public function getCustomerByQq($qq,$id=null)
    {
        $res = DB::table($this->table_name.' as c')
            ->leftJoin('admin_users as a','c.recommend','=','a.id')
            ->select('a.name', "c.id")
            ->where("c.mall_id", request()->mallData["id"]);
        $res->where(function ($query) use ($qq) {
            $query->where("c.qq", "REGEXP", "(^|,)(".implode("|", $qq).")($|,)");
        });
        if (!empty($id)){
            $res->where('c.id','<>',$id);
        }
        $result = $res->orderBy("id", "desc")->first();
        $result = json_decode(json_encode($result),true);
        return $result;
    }

    //通过公司名获取客户信息
    public function getCustomerByCompany($company,$id=null)
    {
        $res = DB::table($this->table_name.' as c')
            ->leftJoin('admin_users as a','c.recommend','=','a.id')
            ->select('a.name', "c.id")
            ->where("c.mall_id", request()->mallData["id"]);
        $res->where(function ($query) use ($company) {
            $query->where("c.company", "REGEXP", "(^|,)(".implode("|", $company).")($|,)");
        });
        if (!empty($id)){
            $res->where('c.id','<>',$id);
        }
        $result = $res->orderBy("id", "desc")->first();
        $result = json_decode(json_encode($result),true);
        return $result;
    }

    public function getCustomerByTencentId($tencentId,$id=null)
    {
        $res = DB::table($this->table_name.' as c')
            ->leftJoin('admin_users as a','c.recommend','=','a.id')
            ->select('a.name', "c.id")
            ->where("c.mall_id", request()->mallData["id"]);
        $res->where(function ($query) use ($tencentId) {
            $query->where("c.tencent_id", "REGEXP", "(^|,)(".implode("|", $tencentId).")($|,)");
        });
        if (!empty($id)){
            $res->where('c.id','<>',$id);
        }
        $result = $res->orderBy("id", "desc")->first();
        $result = json_decode(json_encode($result),true);
        return $result;
    }

    //通过微信获取客户信息
    public function getCustomerByWechat($wechat,$id=null){
        $res = DB::table($this->table_name.' as c')
            ->leftJoin('admin_users as a','c.recommend','=','a.id')
            ->select('a.name', "c.id")
            ->where("c.mall_id", request()->mallData["id"]);
        $res->where(function ($query) use ($wechat) {
            $query->where("c.wechat", "REGEXP", "(^|,)(".implode("|", $wechat).")($|,)");
        });
        if (!empty($id)){
            $res->where('c.id','<>',$id);
        }
        $result = $res->orderBy("id", "desc")->first();
        $result = json_decode(json_encode($result),true);
        return $result;
    }

    /* 通过邮箱获取客户 */
    public function getCustomerByEmail($email,$id=null){
        $res = DB::table($this->table_name.' as c')
            ->leftJoin('admin_users as a','c.recommend','=','a.id')
            ->select('a.name', "c.id")
            ->where("c.mall_id", request()->mallData["id"]);
        $res->where(function ($query) use ($email) {
            $query->where("c.email", "REGEXP", "(^|,)(".implode("|", $email).")($|,)");
        });
        if (!empty($id)){
            $res->where('c.id','<>',$id);
        }
        $result = $res->orderBy("id", "desc")->first();
        $result = json_decode(json_encode($result),true);
        return $result;
    }

    public function validCustomerRepeat($fields,$id=null)
    {
        $return_data = [
            'is_repeat' => 0,
            'returnData' => '',
            "id" => 0
        ];
        if(!empty($fields["mobile"])){
            $mobile = explode(',',$fields["mobile"]);
            if(count($mobile) != count(array_unique($mobile))){
                $return_data['is_repeat'] = 1;
                $return_data['returnData'] = ['code'=>311,"msg"=>"手机号不能重复"];
                $return_data['id'] = 0;
                return $return_data;
            }
            $mobile = array_unique($mobile);
            $res = $this->getCustomerByMobile($mobile,$id);
            if ($res){
                $return_data['is_repeat'] = 1;
                $return_data['returnData'] = ['code'=>312,"msg"=>"手机号已存在，跟进人员：".$res["name"] ?? ''];
                $return_data['id'] = $res["id"] ?? 0;
                return $return_data;
            }
        }
        if(!empty($fields["email"])){
            $email = explode(',',$fields["email"]);
            $email = array_unique($email);
            $res = $this->getCustomerByEmail($email,$id);
            if ($res){
                $return_data['is_repeat'] = 1;
                $return_data['returnData'] = ['code'=>313,"msg"=>"邮箱已存在" . "，跟进人员：{$res["name"]}"];
                $return_data['id'] = $res["id"];
                return $return_data;
            }
        }
        if(!empty($fields["qq"])){
            $qq = explode(',',$fields["qq"]);
            $qq = array_unique($qq);
            $res = $this->getCustomerByQq($qq,$id);
            if ($res){
                $return_data['is_repeat'] = 1;
                $return_data['returnData'] = ['code'=>314,"msg"=>"腾讯云ID已存在" . "，跟进人员：{$res["name"]}"];
                $return_data['id'] = $res["id"];
                return $return_data;
            }
        }
        if(!empty($fields["wechat"])){
            $wechat = explode(',',$fields["wechat"]);
            $wechat = array_unique($wechat);
            $res = $this->getCustomerByWechat($wechat,$id);
            if ($res){
                $return_data['is_repeat'] = 1;
                $return_data['returnData'] = ['code'=>315,"msg"=>"微信号已存在" . "，跟进人员：{$res["name"]}"];
                $return_data['id'] = $res["id"];
                return $return_data;
            }
        }
        if(!empty($fields["company"])){
            $company = explode(',',$fields["company"]);
            $company = array_unique($company);
            $res = $this->getCustomerByCompany($company,$id);
            if ($res){
                $return_data['is_repeat'] = 1;
                $return_data['returnData'] = ['code'=>316,"msg"=>"公司名已存在" . "，跟进人员：{$res["name"]}"];
                $return_data['id'] = $res["id"];
                return $return_data;
            }
        }
        if(!empty($fields["tencent_id"])){
            $tencent_id = explode(',',$fields["tencent_id"]);
            $tencent_id = array_unique($tencent_id);
            $res = $this->getCustomerByTencentId($tencent_id,$id);
            if ($res){
                $return_data['is_repeat'] = 1;
                $return_data['returnData'] = ['code'=>317,"msg"=>"qq已存在" . "，跟进人员：{$res["name"]}"];
                $return_data['id'] = $res["id"];
                return $return_data;
            }
        }
        return $return_data;
    }

    public function getCustomerListWithFilter($fields)
    {
        $db = Customer::query()
            ->where("mall_id", request()->mallData["id"])
            ->with(["follow" => function($query){
                $query->leftJoin("admin_users as a", "a.id", "=", "customer_follow.follow_id")
                    ->select("customer_follow.*", "a.name");
            }, "user" => function($query) use($fields){
                if($fields["repeatId"]){
                    $query->where("id", "<>", $fields["repeatId"]);
                }
                $query->select("name as admin_name", "id");
            }, "group" => function($query){
                $query->leftJoin("group_list as gl", "gl.id", "=", "customer_group.group_id")
                    ->select("customer_group.*", "gl.name", "gl.id");
            }, "communicateLogOne" => function($query){
                $query->orderBy("id", "desc")->select(["contentlog", "member_id"]);
            }, "sources" => function($query){
                $query->select(["customer_id", "content", "create_time"])->orderBy("id", "desc");
            }]);
        if(!empty($fields["group_id"])){
            $temp = CustomerGroup::query()->where("group_id", $fields["group_id"])->select("customer_id")->get()->toArray();
            $db->whereIn('id', array_column($temp, "customer_id"));
        }
        if ($fields['admin_id'] != '') {
            $adminUserModel = new UserBase();
            $user_list = $adminUserModel->getAdminSubuser($fields['admin_id']);
            $temp = Follow::query()->whereIn("follow_id", $user_list)->select("customer_id")->get()->toArray();
            if(!empty($temp)) {
                $db->whereRaw("(`recommend` in (".implode(",", $user_list).") or `id` in (".implode(",", array_column($temp, "customer_id"))."))");
            }else{
                $db->whereIn('recommend', $user_list);
            }
        }
        if ($fields['source'] != '') {
            $db->where('source', '=', $fields['source']);
        }
        if ($fields['cust_state'] != '') {
            $db->where('cust_state', $fields['cust_state']);
        }
        if ($fields['recommend'] != '') {
            $db->where('recommend', $fields['recommend']);
        }
        if ($fields['progress'] != '') {
            $db->where('progress', $fields['progress']);
        }
        if ($fields['contact_status'] != '') {
            if ($fields['contact_status'] == '未联系') {
                $db->where('cust_state', '!=', "1");
                $db->where(function ($query) {
                    $query->whereNull('contact_next_time')
                        ->orwhere('contact_next_time', '=', "0000-00-00 00:00:00");
                });
            }
            if ($fields['contact_status'] == '逾期') {
                $db->where('contact_next_time', '<', substr(Carbon::now()->toDateTimeString(), 0, 10) . ' 00:00:00');
                $db->where('contact_next_time', '!=', "0000-00-00 00:00:00");
                $db->where('cust_state', '!=', "1");
            }
            if ($fields['contact_status'] == '即将逾期') {
                $db->whereBetween('contact_next_time',[date('Y-m-d',time()) . ' 00:00:00',date('Y-m-d',(time()+3*86400)).' 23:59:59']);
                $db->where('contact_next_time', '!=', "0000-00-00 00:00:00");
                $db->where('cust_state', '!=', "1");
            }
        }
        if (isset($fields['list']) && count($fields['list']) > 0) {
            $db->whereIn('id', $fields['list']);
        }
        if ($fields['next_start_time'] != '' && $fields['next_end_time'] != '') {
            $db->whereBetween('contact_next_time', [$fields['next_start_time'], $fields['next_end_time']]);
        } else if ($fields['next_start_time'] != '' && $fields['next_end_time'] == '') {
            $db->where('contact_next_time', '>=', $fields['next_start_time']);
        } else if ($fields['next_start_time'] == '' && $fields['next_end_time'] != '') {
            $db->where('contact_next_time', '=<', $fields['next_end_time']);
        }
        if ($fields['start_time'] != '' && $fields['end_time'] != '') {
            $db->whereBetween('created_at', [$fields['start_time'], $fields['end_time']]);
        } else if ($fields['start_time'] != '' && $fields['end_time'] == '') {
            $db->where('created_at', '>=', $fields['start_time']);
        } else if ($fields['start_time'] == '' && $fields['end_time'] != '') {
            $db->where('created_at', '=<', $fields['end_time']);
        }
        if(!empty($fields['searchKey']) && !empty($fields['searchValue'])){
            if($fields["searchKey"] == "mobile"){
                $db->where($fields['searchKey'], 'LIKE', "%{$fields['searchValue']}%")
                    ->orWhere("spare_mobile", "LIKE", "%{$fields['searchValue']}%");
            }else {
                $db->where($fields['searchKey'], 'LIKE', "%{$fields['searchValue']}%");
            }
        }
        if ($fields['sortName'] == 'contact_next_time') {
            $db->whereNotNull('contact_next_time');
        }
        if($fields["repeatStatus"] != '1'){ // 去重复
            $db->groupBy("qq","mobile");
        }
        if($fields["sortName"] == "assign_time"){
            $fields["sortOrder"] = $fields["sortOrder"] == 'desc' ? "asc" : "desc";
        }//echo "<pre>";print_r($param);die("2");
        $countDb = clone $db;
        $data['total'] = $countDb->count();
        $data['rows'] = [];
        $result = $db->skip($fields['start'])->take($fields['pageSize'])->orderBy($fields['sortName'], $fields['sortOrder'])->get()->toArray();
        if (!$result) {
            return $data;
        }
        $data['rows'] = $result;
        return $data;
    }

    /* 添加客户 */
    public function customerInsert($data)
    {
        DB::beginTransaction();
        try {
            $data['created_at'] = Carbon::now()->toDateTimeString();
            $data['updated_at'] = Carbon::now()->toDateTimeString();
            $data['assign_time'] = time();
            $data['source_time'] = Carbon::now()->toDateTimeString();
            $data["mall_id"] = request()->mallData["id"];
            $res_id = DB::table($this->table_name)->insertGetId($data);
            if(!$res_id){
                DB::rollback();
                return false;
            }
            $data['member_id'] = $res_id;
            $res = $this->_afterCustomerInsert($data);
            if(!$res){
                DB::rollback();
                return false;
            }
            DB::commit();
            return $res_id;
        } catch(\Illuminate\Database\QueryException $ex) {
            DB::rollback();
            return false;
        }
    }

    /* 修改客户 */
    public function customerUpdate($id,$data)
    {
        $customerData = $this->getCustomerByID($id);
        if(!is_array($customerData)){
            return false;
        }
        DB::beginTransaction();
        try {
            $data['updated_at'] = Carbon::now()->toDateTimeString();
            $res = DB::table($this->table_name)->where('id',$id)->update($data);
            if(!$res){
                DB::rollback();
                return false;
            }
            DB::commit();
            return true;
        } catch(\Illuminate\Database\QueryException $ex) {
            DB::rollback();
            return false;
        }
    }

    /* 客户批量修改 */
    public function customerBatchUpdate($field,$data)
    {
        $data["updated_at"] = Carbon::now()->toDateTimeString();
        $res = DB::table($this->table_name)->where("mall_id", request()->mallData["id"]);
        if(is_array($field)){
            foreach ($field as $key=>$value){
                if($value[1]=='in'){
                    $res->whereIn($value[0],$value[2]);
                }else{
                    $res->where($value[0],$value[1],$value[2]);
                }
            }
        }
        $result = $res->update($data);
        if(!$result){
            return false;
        }
        return true;
    }

    /* 删除客户 */
    public function customerDelete($id){
        DB::beginTransaction();
        try {
            $res = DB::table($this->table_name)->where('id',$id)->delete();
            if(!$res){
                DB::rollback();
                return false;
            }
            $log = DB::table('assign_log')->where('member_id',$id)->first();
            if ($log){
                $del_assign_log_res = DB::table('assign_log')->where('member_id',$id)->delete();
                if(!$del_assign_log_res){
                    DB::rollback();
                    return false;
                }
            }
            DB::table('communicationlog')->where('member_id',$id)->delete();
            DB::commit();
            return true;
        } catch(\Illuminate\Database\QueryException $ex) {
            DB::rollback();
            return $ex->getMessage();
        }
    }

    //批量删除
    public function customerDeleteAll($arr){
        DB::beginTransaction();
        try {
            $res = DB::table($this->table_name)->whereIn('id',$arr)->delete();
            if(!$res){
                DB::rollback();
                return false;
            }
            $del_assign_log_res =DB::table('assign_log')->whereIn('member_id',$arr)->delete();
            DB::table('communicationlog')->whereIn('member_id',$arr)->delete();
            if(!$del_assign_log_res){
                DB::rollback();
                return false;
            }
            DB::commit();
            return true;
        } catch(\Illuminate\Database\QueryException $ex) {
            DB::rollback();
            return $ex->getMessage();
        }
    }

    /* 添加客户后续处理 */
    private function _afterCustomerInsert($data)
    {
        $tmp_data = array(
            'member_id' => $data['member_id'],
            'assign_uid' => $data['recommend'],
            'assign_touid' => $data['recommend'],
            'assign_name' => $data['addperson'],
            'assign_admin' => $data['addperson'],
            'operation_uid' => $data['recommend']
        );
        $tmp_data["mall_id"] = request()->mallData["id"];
        $assignLogModel = new AssignLog();
        $res = $assignLogModel->assignLogInsert($tmp_data);
        if(!$res){
            return false;
        }
        return true;
    }

    /* 获取最新客户ID */
    public function getMaxCustomerID()
    {
        $id = DB::table($this->table_name)->where("mall_id", request()->mallData["id"])->max('id');
        return $id;
    }
    /* 获取用户客户数 */
    public function getCustomNumber($id, $date = ''){
        if(!empty($date))
            return DB::table($this->table_name)->where("mall_id", request()->mallData["id"])->where('created_at','LIKE','%'.$date.'%')->where('recommend','=',$id)->count();
        else
            return DB::table($this->table_name)->where("mall_id", request()->mallData["id"])->where('recommend','=',$id)->count();
    }

    public function getCustomerOverCount($id)
    {
        $data = DB::table($this->table_name)
            ->where('recommend','=',$id)
            ->where("mall_id", request()->mallData["id"])
            ->where('contact_next_time','<',date('Y-m-d 00:00:00',time()))
            ->where('contact_next_time', '!=', "0000-00-00 00:00:00")
            ->where('cust_state', '!=', "1")
            ->count();
        return $data;
    }

    public function getExchangeRecommend($id, $data){
        $data['updated_at'] = Carbon::now()->toDateTimeString();
        $res = DB::table($this->table_name);
        $res_ext = DB::table('member_extend');
        if(is_array($id)){
            $res->whereIn("recommend",$id);
            $res_ext->whereIn("recommend",$id);
        }else{
            $res->where("recommend",$id);
            $res_ext->where("recommend",$id);
        }
        $res = $res->update($data);
        $data['update_time'] =$data['updated_at'];
        unset($data["updated_at"]);
        $res_ext->update($data);
        if($res || $res === 0){
            return true;
        }
        return false;
    }

    //修改客户信息记录旧信息
    public function updateCustomerLog($admin_id,$admin_name,$c_id){
        $customerData = Customer::query()->where("id", $c_id)->with(["follow" => function ($query) {
            $query->leftJoin("admin_users as a", "a.id", "=", "customer_follow.follow_id")
                ->select("customer_follow.*", "a.name");
        }])->first()->toArray();
        if(!empty($customerData["follow"])){
            foreach ($customerData["follow"] as $item){
                $customerData["follow_list"][] = $item["name"];
            }
            $customerData["follow_list"] = implode("，", $customerData["follow_list"]);
        }
        unset($customerData["follow"]);
        $data['c_id'] = $c_id;
        $data['u_id'] = $admin_id;
        $data['u_name'] = $admin_name;
        $data['c_info'] = json_encode($customerData);
        $data['created_at'] = Carbon::now()->toDateTimeString();
        $data["mall_id"] = request()->mallData["id"];
        DB::table('customer_log')->insert($data);
    }

    //获取修改客户记录
    public function customerLog($id){
        $res = DB::table('customer_log')->where('c_id',$id)->get();
        if (!$res){
            return false;
        }
        $res = json_decode(json_encode($res),true);
        foreach ($res as &$v){
            $v['c_info'] = json_decode($v['c_info'],true);
        }
        return $res;
    }

    //统计全部人
    public function statisticsCustomers($fields){
        $res = DB::table($this->table_name)
            ->select('id','created_at','source','recommend')->where("mall_id", request()->mallData["id"]);
        if ($fields['start_time'] != '' && $fields['end_time'] != ''){
            $res->whereBetween('created_at',[$fields['start_time'],$fields['end_time']]);
        }
        $total = $res->get();
        $total = json_decode(json_encode($total),true);
        return $total;
    }

    //获取总人数
    public function getCustomerTotal($type = ''){
        $res = DB::table($this->table_name)->select('source',DB::raw('SUM(1) AS total_number'))->where("mall_id", request()->mallData["id"]);
        if ($type != ''){
            $res->groupBy('source');
        }
        $result = $res->get();
        $result = json_decode(json_encode($result),true);
        if ($type != ''){
           return $result;
        }
        return $result[0]['total_number'];
    }

    //获取部门的总客户数
    public function getCompanyCustomerTotal($fields){
        $res = DB::table($this->table_name)
            ->select('source',DB::raw('SUM(1) AS total_number'))->where("mall_id", request()->mallData["id"])->whereIn('recommend',$fields['ids']);
        if ($fields['type'] != ''){
            $res->groupBy('source');
        }
        $result = $res->get();
        $result = json_decode(json_encode($result),true);
        if ($fields['type'] != ''){
            return $result;
        }
        return $result[0]['total_number'];
    }

    //获取部门下的总激活人数
    public function getCustomerActivationTotal($fields){
        $res = DB::table($this->table_name)
            ->select('source',DB::raw('SUM(1) AS total_number'))
            ->where('cust_state',1)
            ->where("mall_id", request()->mallData["id"])
            ->whereIn('recommend',$fields['ids']);
        if ($fields['type'] != ''){
            $res->groupBy('source');
        }
        $result = $res->get();
        $result = json_decode(json_encode($result),true);
        if ($fields['type'] != ''){
            return $result;
        }
        return $result[0]['total_number'];
    }

    //根据管理员获取分配人数
    public function getAdminCustomerNumber(){
        $db = DB::table($this->table_name)->select(DB::raw('COUNT(*) AS total_customer'),'recommend')->where("mall_id", request()->mallData["id"])->groupBy('recommend')->get();
        $db = json_decode(json_encode($db),true);
        return $db;
    }

}
